#IMPORTANT: start with the table name on first line and end with newline
#TODO: make a func to organize tables
from PyQt4 import Qt
from PyQt4.QtSql import *
from PyQt4.QtCore import *


class sqlImporter(QSqlTableModel):
	'''one class to hold and rule all data(or atleast all within the scope)''' 
	
	def __init__(self,db,parent=None,filename=None):
		QSqlTableModel.__init__(self,parent,db);
		self.setEditStrategy(QSqlTableModel.OnFieldChange)
		
		self._headers=[]
		self._searchables=[]
		self._database=db
		self._error="There appear to be no errors"
		
		self.loadFile(filename)
		
		#get the id numbers of the searchable rows
		for i,j in enumerate(self._headers):
			if j[-1]=="k":
				self._searchables+=[i]
	
	def filterWords(self,ls,oper=" like "):
		'''a func to take a list of words and allow only them to be visible in the table'''
		filters=[]
		tmp=[]
		
		if oper==" like ":	#if we are guessing and do not need to be percise
			for i in ls:
				tmp+=["%"+i+"%"]	#wildcard time!
		ls=tmp
		
		#now the filter begins
		#make the filters into an sql phrase that searches all columns for each word
		for j in ls:
			tmp=[]
			for i in self._searchables:
				tmp+=[str(self._headers[i])+oper+"'"+str(j)+"'"]
			filters+=["("+" or ".join(tmp)+")"]
		filters=" and ".join(filters)
		
		
		#filters is  now a python string
		self.setFilter(filters)
		self.select()
		return filters
		
	
	def importerError(self,text=-1):
		'''prints or gets last error(no arg just returns). Returns the error anyway'''
		if text==-1:
			return "Error: "+self.lastError().text()
		else:
			_error=text
			return _error
	
	
	
	def createTable(self,table,ls,database):
		'''creates table with name @table and titles of colums @ls in @database'''
		query=QSqlQuery(database)
		q="create table "+table+"("+ls.join(",")+")"
		return query.exec_(q)
	
	
	
	def listItems(self,cin):	#returns a QStringList
		'''returns a QStringList with simplified (see QString doc) the values in the next line'''
		cin.skipWhiteSpace()
		line=cin.readLine()
		if line.isEmpty():
			return None
		tmp=line.split(",")
		for i in range(tmp.count()):
			tmp[i]=tmp[i].simplified()
		return tmp
	
	
	def loadFile(self,filename):
		'''just load filename over the previous table(might be a new table). In case of error returns false'''
		#open read only file on stream cin
		if filename==None:
			print "No file to import, retreating to default database, table \"%s\"" %self.database().tables()[0]
			self.setTable(self.database().tables()[0])
			for i in range(self.columnCount()):
				self._headers+=[self.headerData(i,1).toString()]
			return self.select()
		
		file=QFile(filename)
		file.open(QIODevice.ReadOnly)
		cin=QTextStream(file)
		
		#create the table
		table=self.listItems(cin)
		if not table:
			self.importerError("File seems to be empty")
			return False
		self._headers=self.listItems(cin)
		print self._headers[0]
		self.createTable(table[0],self._headers,self._database)
		self.setTable(table[0])
		
		#fill in the table
		arg=self.listItems(cin)
		while arg:
			self.insertRows(0,1)	#insert an empty row in the beginning
			for i,dt in enumerate(arg):
				if not self.setData(self.index(0,i),dt):
					self.importerError(self.lastError().text())
					return False
			self.submitAll()
			arg=self.listItems(cin)
		return True
	
	def searchableColumns(self):
		return self._searchables

	def getHeaders(self):
		return self._headers
		
	def getRowData(self,x):
		self.select()
		if x>=self.rowCount():
			return None
		tmp=[]
		for i in range(self.record(x).count()):
			tmp+=[self.record(x).field(i).value().toString()]
		return tmp
	
	def getStrData(self,i,j):
		'''retruns QString at i,j'''
		self.select()
		if self.rowCount()<=i or j>=len(self._headers):
			return None
		return self.data(self.index(i,j)).toString()





#db=QSqlDatabase.addDatabase("QSQLITE")
#db.setDatabaseName(":memory:")
#ok=db.open()
#if not ok:
#	print "There was an error opening tha database"
#	exit()
#table=sqlImporter("test.txt",db)
#table.select()
#
#inp=raw_input("coma separated: ").split(",")
#table.filterWords(inp)
#ok=table.getRowData(0)
#i=0
#print ok
#while not ok==None:
#	i+=1
#	ok=table.getRowData(i)
#	print ok 
